package com.bifrost.repository;

import com.bifrost.entity.ProductBrand;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigInteger;
import java.util.Date;
import java.util.List;

/**
 * @author rangf
 * @date 2017/11/6
 */
public interface ProductBrandRepository extends JpaRepository<ProductBrand, Long> , JpaSpecificationExecutor<ProductBrand>{
    /**
     * 获取所有商品品牌按照拼音排序
     *
     * @return List
     */
    @Query(value = "SELECT * FROM PRODUCT_BRAND B  WHERE  ISNULL(B.IS_ARCHIVED) OR B.IS_ARCHIVED=0 ORDER BY CONVERT(B.BRAND_NAME USING GBK) ASC", nativeQuery = true)
    List<ProductBrand> getProductBrands();

    @Query(value = "SELECT DISTINCT(P.PRODUCT_BRAND_ID) FROM PRODUCT P", nativeQuery = true)
    List<BigInteger> getProductOfBrands();

    /**
     * 获取某品类下所有品牌
     * @param categoryId
     * @return
     */
    @Query(value = "SELECT DISTINCT(P.PRODUCT_BRAND_ID) FROM PRODUCT P WHERE P.CATEGORY = ?1", nativeQuery = true)
    List<BigInteger> getProductBrandsByCategory(Long categoryId);

    @Query(value = "SELECT DISTINCT(P.PRODUCT_BRAND_ID) FROM PRODUCT P WHERE P.CATEGORY IN ?1", nativeQuery = true)
    List<BigInteger> getProductBrandsByCategorys(List<BigInteger> ids);

    @Query(value = "SELECT * FROM PRODUCT_BRAND P WHERE p.PRODUCT_BRAND_ID IN ?1", nativeQuery = true)
    List<ProductBrand> getProductBrandsIn(List<BigInteger> ids);

    @Query(value = "SELECT DISTINCT(P.PRODUCT_BRAND_ID) FROM `PRODUCT` P WHERE P.MERCHANT_ID = ?1", nativeQuery = true)
    List<BigInteger> getStoreBrands(Long merchantId);

    List<ProductBrand> findAllByIsArchivedIsNullOrIsArchivedIsFalse();

    @Query(value = "SELECT count(1) FROM PRODUCT_SKU p WHERE  p.PRODUCT_BRAND_ID =?1 AND p.IS_AVAILABLE=1 " , nativeQuery = true)
    Long countAllByBrand(Long brands);

    @Modifying
    @Query(value = "update product_brand set IS_ARCHIVED=1 AND UPDATED_BY=?1 where PRODUCT_BRAND_ID in (?2)" , nativeQuery = true)
    void batchUpdateArchived(String adminName, List<Long> brandIds);

    @Modifying(clearAutomatically = true)
    @Query(value = "INSERT INTO product_brand (PRODUCT_BRAND_ID, BRAND_NAME,UPDATED_TIMESTAMP) VALUES (?1, ?2,NOW())" , nativeQuery = true)
    @Transactional
    void saveSapBrand(Long brandCode, String ppmcZh);
}
